package billings.classtasks.instructors;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import billings.classtasks.BaseDAO;
import billings.classtasks.DatabaseHelper;
import billings.classtasks.classes.Class;

public class InstructorDAO extends BaseDAO{

	// db fields
	private String[] allColumns = { DatabaseHelper.INSTRUCTOR_COLUMN_ID,
								    DatabaseHelper.INSTRUCTOR_FIRST_NAME,
								    DatabaseHelper.INSTRUCTOR_LAST_NAME,
								    DatabaseHelper.INSTRUCTOR_DEPARTMENT,
								    DatabaseHelper.INSTRUCTOR_EMAIL,
								    DatabaseHelper.INSTRUCTOR_PHONE_NUMBER,
								    DatabaseHelper.INSTRUCTOR_OFFICE_BUILDING,
								    DatabaseHelper.INSTRUCTOR_OFFICE_NUMBER,
								    DatabaseHelper.INSTRUCTOR_TYPE_ID_FK };
	
	private String[] allTypeColumns = { DatabaseHelper.INSTRUCTOR_TYPE_COLUMN_ID,
										DatabaseHelper.INSTRUCTOR_TYPE_DESCRIPTION,
										DatabaseHelper.INSTRUCTOR_TYPE_ABBREVIATION };

	public InstructorDAO(Context context) {
		super(context);
	}

	public Instructor createInstructor(String firstName, String lastname, String department, 
			                      String email, String phoneNumber, String officeBuilding, 
			                      String officeNumber, int instructorTypeId) {

		// setup content values
		ContentValues values = new ContentValues();
		values.put(DatabaseHelper.INSTRUCTOR_FIRST_NAME, firstName);
		values.put(DatabaseHelper.INSTRUCTOR_LAST_NAME, lastname);
		values.put(DatabaseHelper.INSTRUCTOR_DEPARTMENT, department);
		values.put(DatabaseHelper.INSTRUCTOR_EMAIL, email);
		values.put(DatabaseHelper.INSTRUCTOR_PHONE_NUMBER, phoneNumber);
		values.put(DatabaseHelper.INSTRUCTOR_OFFICE_BUILDING, officeBuilding);
		values.put(DatabaseHelper.INSTRUCTOR_OFFICE_NUMBER, officeNumber);
		values.put(DatabaseHelper.INSTRUCTOR_TYPE_ID_FK, instructorTypeId);

		long insertId = database.insert(DatabaseHelper.INSTRUCTOR_TABLE, null, values);
		Cursor cursor = database.query(DatabaseHelper.INSTRUCTOR_TABLE, allColumns,
									   DatabaseHelper.INSTRUCTOR_COLUMN_ID + " = " + insertId, null, null, null, null);

		cursor.moveToFirst();
		Instructor newInstructor = cursorToInstructor(cursor);
		cursor.close();

		return newInstructor;
	}

	public InstructorType createInstructorType(String description, String abbreviation) {
		// setup content values
		ContentValues values = new ContentValues();
		values.put(DatabaseHelper.INSTRUCTOR_TYPE_DESCRIPTION, description);
		values.put(DatabaseHelper.INSTRUCTOR_TYPE_ABBREVIATION, abbreviation);

		long insertId = database.insert(DatabaseHelper.INSTRUCTOR_TYPE_TABLE, null, values);
		Cursor cursor = database.query(DatabaseHelper.INSTRUCTOR_TYPE_TABLE, allTypeColumns, 
				                       DatabaseHelper.INSTRUCTOR_TYPE_COLUMN_ID + " = " + insertId, null, null, null, null);

		cursor.moveToFirst();
		InstructorType newInstructorType = cursorToInstructorType(cursor);
		cursor.close();

		return newInstructorType;
	}

	public int updateInstructor(int instructorId, String firstName, String lastname, String department, 
            					String email, String phoneNumber, String officeBuilding, 
            					String officeNumber, int instructorTypeId) {
		long id = instructorId;
		ContentValues cvs = new ContentValues();
		cvs.put(DatabaseHelper.INSTRUCTOR_FIRST_NAME, firstName);
		cvs.put(DatabaseHelper.INSTRUCTOR_LAST_NAME, lastname);
		cvs.put(DatabaseHelper.INSTRUCTOR_DEPARTMENT, department);
		cvs.put(DatabaseHelper.INSTRUCTOR_EMAIL, email);
		cvs.put(DatabaseHelper.INSTRUCTOR_PHONE_NUMBER, phoneNumber);
		cvs.put(DatabaseHelper.INSTRUCTOR_OFFICE_BUILDING, officeBuilding);
		cvs.put(DatabaseHelper.INSTRUCTOR_OFFICE_NUMBER, officeNumber);
		cvs.put(DatabaseHelper.INSTRUCTOR_TYPE_ID_FK, instructorTypeId);

		int rowsUpdated = database.update(DatabaseHelper.INSTRUCTOR_TABLE, cvs,
				DatabaseHelper.INSTRUCTOR_COLUMN_ID + " = ",
				new String[] { id + "" });

		return rowsUpdated;
	}

	public int updateInstructorType(int instructorTypeId, String description, String abbreviation) {
		
		long id = instructorTypeId;
		ContentValues cvs = new ContentValues();
		cvs.put(DatabaseHelper.INSTRUCTOR_TYPE_DESCRIPTION, description);
		cvs.put(DatabaseHelper.INSTRUCTOR_TYPE_ABBREVIATION, abbreviation);
		
		int rowsUpdated = database.update(DatabaseHelper.INSTRUCTOR_TYPE_TABLE, cvs,
				DatabaseHelper.INSTRUCTOR_TYPE_COLUMN_ID + " = ", new String[] { id
						+ "" });

		return rowsUpdated;
	}
	
	public void deleteInstructor(int instructorId) {
		int ret = database.delete(DatabaseHelper.INSTRUCTOR_TABLE,
				DatabaseHelper.INSTRUCTOR_COLUMN_ID + " = " + instructorId, null);
		if (ret > 0) {
			System.out.println("Instructor deleted with id: " + instructorId);
		} else {
			System.out.println("NO Instructor was deleted with id: " + instructorId);
		}
	}

	public void deleteInstructorType(InstructorType instructorType){
		long id = instructorType.getId();
		int ret = database.delete(DatabaseHelper.INSTRUCTOR_TYPE_TABLE,
				DatabaseHelper.INSTRUCTOR_TYPE_COLUMN_ID + " = " + id, null);
		if (ret > 0) {
			System.out.println("Instructor Type deleted with id: " + id);
		} else {
			System.out.println("NO Instructor Type was deleted with id: " + id);
		}
	}
	
	public List<Instructor> getAllInstructors() {
		List<Instructor> instructors = new ArrayList<Instructor>();

		Cursor cursor = database.query(DatabaseHelper.INSTRUCTOR_TABLE, allColumns, null, null, null, null, null);

		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Instructor instructor = cursorToInstructor(cursor);
			instructors.add(instructor);
			cursor.moveToNext();
		}

		cursor.close();

		return instructors;
	}
	
	public List<InstructorType> getAllInstructorTypes() {
		List<InstructorType> instructorTypes = new ArrayList<InstructorType>();

		Cursor cursor = database.query(DatabaseHelper.INSTRUCTOR_TYPE_TABLE, allTypeColumns, null, null, null, null, null);

		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			InstructorType instructorType = cursorToInstructorType(cursor);
			instructorTypes.add(instructorType);
			cursor.moveToNext();
		}

		cursor.close();

		return instructorTypes;
	}

	private Instructor cursorToInstructor(Cursor cursor) {
		Instructor instructor = new Instructor();
		
		instructor.setId(cursor.getInt(0));
		instructor.setFirstName(cursor.getString(1));
		instructor.setLastName(cursor.getString(2));
		instructor.setDepartment(cursor.getString(3));
		instructor.setEmail(cursor.getString(4));
		instructor.setPhoneNumber(cursor.getString(5));
		instructor.setOfficeBuilding(cursor.getString(6));
		instructor.setOfficeNumber(cursor.getString(7));
		instructor.setInstructorTypeId(cursor.getInt(8));		
		
		return instructor;
	}
	
	private InstructorType cursorToInstructorType(Cursor cursor) {
		InstructorType instructorType = new InstructorType();
		
		instructorType.setId(cursor.getInt(0));
		instructorType.setDescription(cursor.getString(1));
		instructorType.setAbbreviation(cursor.getString(2));
		
		return instructorType;
	}
}
